package com.deng.etl.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import com.deng.etl.common.IEtlInterface;
import com.deng.etl.common.EtlLog;
//制空   t_org_organization_workload_month
public class ImportBedICU_count extends ImportBase implements IEtlInterface {
	
	public static String COLUMNS=" sum(org_item_count) as count ";
	public static String SELECT="select "+COLUMNS+"  from t_org_organization_item_count_month " +
		"	where org_item_name = '监护病房床位费' and  org_operate_organization_id = '18I1' " +
		"	and stat_time >= ? and stat_time < ?" +
		"	group by org_operate_organization_id,org_operate_organization_name";
	
	public static String INSERT="insert into t_org_organization_workload_month " +
		"(stat_time,org_operate_organization_id,org_operate_organization_name," +
		"create_time,ip_in_count,ip_out_count," +
		"op_count,cure_count,bed_use_rate,bed_use_count,bed_open_count) " +
		"values(?,'18I1','ICU病区',?,null,null,null,null,null,?,null)";
	
	public static String UPDATE="update t_org_organization_workload_month " +
			"	set bed_use_count=(case when bed_use_count is null then 0 else bed_use_count end)+? where " +
			"	stat_time=? and org_operate_organization_id=?";
	
	public static String DELETE="update t_org_organization_workload_month set bed_use_count=null where stat_time>=? and stat_time<?";

	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate){
		System.out.println("从："+fromDate+"   到："+toDate);
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			sourceConn = targetConn;
			PreparedStatement pst=sourceConn.prepareStatement(SELECT);
			System.out.println("-SELECT-------"+SELECT);
			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
			PreparedStatement pst_update=targetConn.prepareStatement(UPDATE);
			try {
				pst.setDate(1, getSqlDate(format_s.parse(fromDate)));
				pst.setDate(2, getSqlDate(format_s.parse(toDate)));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				String or_code="18I1";
				String or_name="ICU病区";
//				int year=rs.getInt("year");
//				int month=rs.getInt("month");
//				java.sql.Date insertdate=new java.sql.Date(year-1900,month-1,1);
				java.sql.Date insertdate=new java.sql.Date(
						Integer.valueOf(fromDate.split("-")[0])-1900,
						Integer.valueOf(fromDate.split("-")[1])-1,1);
				boolean flag=checkExist(targetConn, insertdate, or_code);
				if(flag){
					pst_update.setInt(1, rs.getInt("count"));
					pst_update.setDate(2, insertdate);
					pst_update.setString(3, or_code);
					pst_update.executeUpdate();
				}
				else{					
					pst_insert.setDate(1, insertdate);
//					pst_insert.setString(2, or_code);
//					pst_insert.setString(3, or_name);
					java.util.Date date1=new java.util.Date();
					pst_insert.setTimestamp(2, getTimestamp(date1));
					pst_insert.setInt(3, rs.getInt("count"));
					
					pst_insert.executeUpdate();
				}
			}
			rs.close();
			pst.close();
			pst_update.close();
			pst_insert.close();
			//this.finishOneTaskETLLog(targetConn, name, toDate);
				
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	public boolean checkExist(Connection conn,java.sql.Date date,String id){
		boolean ret=false;
		try {
			System.out.println("---------checkExist-------"+date.toLocaleString()+";"+id);
			PreparedStatement pst =conn.prepareStatement("select stat_time from t_org_organization_workload_month" +
					" where stat_time=? and org_operate_organization_id=?");
			pst.setDate(1, date);
			pst.setString(2, id);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				ret=true;
			}
			rs.close();
			pst.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	public void deleteData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		// TODO Auto-generated method stub
		System.out.println("从："+fromDate+"   到："+toDate);
		try{
			sourceConn = targetConn;
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
			try {
				pst_delete.setDate(1, getSqlDate(format_s.parse(fromDate)));
				pst_delete.setDate(2, getSqlDate(format_s.parse(toDate)));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			pst_delete.executeUpdate();
			pst_delete.close();
			//EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	public void updateData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		System.out.println("从："+fromDate+"   到："+toDate);
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			deleteData(sourceConn, targetConn,name, fromDate, toDate);
			insertData(sourceConn, targetConn,name, fromDate, toDate);
			//EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
	}
	
	
	
	
//	public static String COLUMNS=" 科室ID,科室名称,year(日期) as year,month(日期) as month,count(床位费) as count ";
//	public static String SELECT="select "+COLUMNS+"  from neusoft.JIXIAO_DAYBEDFEEINFO " +
//		"where 日期>=? and 日期<? group by 科室ID,科室名称,year(日期),month(日期)";
//	
//	public static String INSERT="insert into t_org_organization_workload_month " +
//		"(stat_time,org_operate_organization_id,org_operate_organization_name," +
//		"create_time,ip_in_count,ip_out_count," +
//		"op_count,cure_count,bed_use_rate,bed_use_count,bed_open_count) " +
//		"values(?,?,?,?,null,null,null,null,null,?,null)";
//	
//	public static String UPDATE="update t_org_organization_workload_month set bed_use_count=(case when bed_use_count is null then 0 else bed_use_count end)+? where " +
//			" stat_time=? and org_operate_organization_id=?";
//	
//	public static String DELETE="update t_org_organization_workload_month set bed_use_count=null where stat_time>=? and stat_time<?";
//
//	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate){
//		System.out.println("从："+fromDate+"   到："+toDate);
//		try{
//			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
//	
//			PreparedStatement pst=sourceConn.prepareStatement(SELECT);
//			System.out.println("-SELECT-------"+SELECT);
//			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
//			PreparedStatement pst_update=targetConn.prepareStatement(UPDATE);
//			try {
//				pst.setDate(1, getSqlDate(format_s.parse(fromDate)));
//				pst.setDate(2, getSqlDate(format_s.parse(toDate)));
//			} catch (ParseException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//			ResultSet rs=pst.executeQuery();
//			while(rs.next()){
//				String or_code=rs.getString("科室ID");
//				String or_name=rs.getString("科室名称");
//				int year=rs.getInt("year");
//				int month=rs.getInt("month");
//				java.sql.Date insertdate=new java.sql.Date(year-1900,month-1,1);
//				boolean flag=checkExist(targetConn, insertdate, or_code);
//				if(flag){
//					pst_update.setInt(1, rs.getInt("count"));
//					pst_update.setDate(2, insertdate);
//					pst_update.setString(3, or_code);
//					pst_update.executeUpdate();
//				}
//				else{
//					
//					pst_insert.setDate(1, insertdate);
//					pst_insert.setString(2, or_code);
//					pst_insert.setString(3, or_name);
//					java.util.Date date1=new java.util.Date();
//					pst_insert.setTimestamp(4, getTimestamp(date1));
//					pst_insert.setInt(5, rs.getInt("count"));
//					
//					pst_insert.executeUpdate();
//				}
//			}
//			rs.close();
//			pst.close();
//			pst_update.close();
//			pst_insert.close();
//			//this.finishOneTaskETLLog(targetConn, name, toDate);
//				
//		}
//		catch(SQLException e){
//			e.printStackTrace();
//		}
//	}
//	
//	public boolean checkExist(Connection conn,java.sql.Date date,String id){
//		boolean ret=false;
//		try {
//			System.out.println("---------checkExist-------"+date.toLocaleString()+";"+id);
//			PreparedStatement pst =conn.prepareStatement("select stat_time from t_org_organization_workload_month" +
//					" where stat_time=? and org_operate_organization_id=?");
//			pst.setDate(1, date);
//			pst.setString(2, id);
//			ResultSet rs=pst.executeQuery();
//			if(rs.next()){
//				ret=true;
//			}
//			rs.close();
//			pst.close();
//		
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return ret;
//	}
//	
//	public void deleteData(Connection sourceConn, Connection targetConn,
//			String name, String fromDate, String toDate) {
//		// TODO Auto-generated method stub
//		System.out.println("从："+fromDate+"   到："+toDate);
//		try{
//			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
//			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
//			try {
//				pst_delete.setDate(1, getSqlDate(format_s.parse(fromDate)));
//				pst_delete.setDate(2, getSqlDate(format_s.parse(toDate)));
//			} catch (ParseException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//			pst_delete.executeUpdate();
//			pst_delete.close();
//			//EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
//		}
//		catch(SQLException e){
//			e.printStackTrace();
//		}
//	}
//	
//	public void updateData(Connection sourceConn, Connection targetConn,
//			String name, String fromDate, String toDate) {
//		System.out.println("从："+fromDate+"   到："+toDate);
//		try{
//			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
//			deleteData(sourceConn, targetConn,name, fromDate, toDate);
//			insertData(sourceConn, targetConn,name, fromDate, toDate);
//			//EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
//		}
//		catch(Exception e){
//			e.printStackTrace();
//		}
//	}
//	/**
//	 * @param args
//	 */
//	public static void main(String[] args) {
//		// TODO Auto-generated method stub
//	}
	
}
